﻿-- =============================================
-- Author:		<Author,XFL>
-- Edit date: <Create Date,2012 02 01>
-- Description:	<Description,[人资管理]-[业绩绩效考核]-[项目任务]
--	将原来的存储过程进行了优化,并且提速效果很明显
--	同时,部门查询是 使用了部门编号查询 BMBH >
-- =============================================
CREATE PROCEDURE [dbo].[proc_Employee_JiXiao_ProjectTask_BackUp0306]
	(
		@CompanyId int,
		@DepartmentId int,
		--@EmpState int,
		@StartDate nvarchar(50),
		@EndDate nvarchar (50),
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex int
	)
AS
	Begin

			declare @bmbh_T  varchar(50)
			set @bmbh_T = (select bmbh  FROM department WHERE id=@departmentId)		
			declare @indextable table(id int identity(1,1),nid int)
			insert into @indextable(nid) Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T;

		declare @list_table_1 table(
		id int identity(1,1)
		,username  nvarchar(50)
		,company nvarchar(50) /*公司名称*/
		,department nvarchar(50) /*部门名称*/
		,ename nvarchar(50) /*部门名称*/
		,task_ncount int	
		,task_ycount int

		)

		;WITH list As
		(
		select 	
		username		
		,(select companyname from company where company.id = e.companyid) as company
		,(select dname from department where department.id=e.departmentid) as department
		,e.ename
		,(select count(pt.id) from project_task pt where pt.execperson=e.username and pt.execperson <>''  and pt.isfinish=0 and (Convert(varchar(10),pt.enddate,120)>=@StartDate and Convert(varchar(10),pt.enddate,120)<=@EndDate)) as task_ncount
		,(select count(pt.id) from project_task pt where pt.execperson=e.username and pt.execperson <>''  and pt.isfinish=1 and (Convert(varchar(10),pt.enddate,120)>=@StartDate and Convert(varchar(10),pt.enddate,120)<=@EndDate)) as task_ycount
		--,(Case When State<>5 Then (select  1 ) Else ( select  2  ) End) as state
		from employee e	
		where (@companyid=0 or e.companyid=@companyid) 
		and (@DepartmentId = 0 or e.departmentid in(select c.nid from @indextable c)) 
		and e.ename like  '%'+@SText+'%' 
		and e.username <>'' and state <> 5
		) 

		insert into @list_table_1(
		username
		,company 
		,department 
		,ename 
		,task_ncount 
		,task_ycount 
		) 
		select 
		username
		,company 
		,department 
		,ename 
		,task_ncount 
		,task_ycount 
		From list  
		-- where ( @EmpState =0 or list.state = @EmpState)
		order by task_ycount desc , ename;
		-- 上面完成 list 表


		;WITH list_return as (
		Select ROW_NUMBER() OVER (ORDER BY Id) AS Row
		,username
		,company 
		,department 
		,ename 
		,task_ncount 
		,task_ycount 
		from @list_table_1
		)

		select 
		*
		,(select count(0) from list_return ) AS Count_Sum
		,( select sum( Task_Ycount) from list_return) AS Task_Ycount_Sum 
		,( select sum( Task_Ncount) from list_return) AS Task_Ncount_Sum 
		,ISNULL((select top 1 [Employee].state from [Employee] Where [Employee].UserName=[list_return].UserName ),0) as State 
		from list_return  
		Where Row between @StartIndex and @EndIndex  order by row

End
	
	RETURN
